# -*- coding=utf-8 -*-
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import tornado.web
import tornado.httpserver
import tornado.ioloop
import tornado.options
import os.path,os,datetime,sqlite3,sys
sys.path.append(os.path.dirname(os.path.abspath(__file__)))
def dbconn(sql):
    conn = sqlite3.connect("Stock.s3db")
    conn.text_factory = str
    cur = conn.cursor()
    cur.execute(sql)
    conn.commit()
    return cur.fetchall()
    cur.close()
    conn.close()
    
settings = {
    "static_path" : os.path.join(os.path.dirname(__file__), "./static/"),
    #"xsrf_cookies" : True,
    #"cookie_secret" : "AGaYdkL5gEmGeJJFuYh7EQnp2XddfgdfTP",
    "template_path" : os.path.join(os.path.dirname(__file__), "./templates/"),
    "login_url": "/login",
    }
class LoginHandler(tornado.web.RequestHandler):
    def get_current_user(self):
        return self.get_cookie("LoginStock")
class MainHandler(tornado.web.RequestHandler):
    def post(self):
        html = ''
        user = self.get_cookie('LoginStock')
        if user:
            status = "1"
            alluser = dbconn('''select
                    B.UseUser || '-' || A.USERNAME AS UseUSER,
                   A.ORG AS ORG,
                   B.JIGID AS JIGID,
                   B.JIGNAME AS JIGNAME,
                   B.LINE AS LINE,
                   B.USE AS USE,
                   B.OUTTIME AS OUTTIME,
                   B.STATUS AS STATUS,
                   B.ID AS ID
            from
                 USERS A,
                 STOCK B
            WHERE
                 B.UseUser<>''
                 AND B.UseUser=A.UserID
            ''')
            html = u'''
                <div class="table-responsive div">
                <table id="main" class="table table-striped table-bordered table-hover">
                <thead>
                <tr>
                <td>借用者</td>
                <td>單位</td>
                <td>治具ID</td>
                <td>治具名稱</td>
                <td>線別</td>
                <td>用途</td>
                <td>借出時間</td>
                <td>狀態</td>
                </tr>
                </thead>
                <tbody>
                '''
            if len(alluser) >0:
                for i in alluser:
                    STATUS=''
                    if i[7]=='0':
                        STATUS=u'閒置中'
                    elif i[7]=='1':
                        STATUS=u'已借出'
                    html = html + u'''
                    <tr>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    </tr>''' % (i[0],i[1],i[2],i[3],i[4],i[5],i[6],STATUS)
                html = html + u'</tbody></table></div>'
            else:
                html = html + u'<tr><td>目前資料庫中無未歸還記錄！</td></tr></tbody></table></div>'
            self.write(html.encode('utf-8'))
        
    def get(self):
        status = "0"
        user = self.get_cookie('LoginStock')
        if user:status = '1'
        self.render("stock.html",status=status,user=user)
        
class query(LoginHandler):
    @tornado.web.authenticated
    def post(self):
        html = u'''<div class="table-responsive div">
        <table id="querytable" class="table table-striped table-bordered table-hover">
        <thead>
        <tr>
        <td>借用人</td>
        <td>組織</td>
        <td>治具ID</td>
        <td>治具名稱</td>
        <td>治具狀態</td>
        <td>管理</td>
        </tr>
        </thead>
        <tbody>
        '''
        command = self.get_argument('command','')
        SQL = '' 
        if command=='*':
            SQL = 'SELECT UseUser,JIGID,JIGNAME,STATUS,ID FROM stock'
        elif command<>'':
            SQL = "SELECT UseUser,JIGID,JIGNAME,STATUS,ID FROM stock WHERE UseUser='%s' OR JIGID='%s' OR JIGNAME='%s'" % (command,command,command)
        stock = dbconn(SQL)
        if stock:
            for i in stock:
                useuser = i[0]
                ORG = ''
                JIGID = i[1]
                JIGNAME = i[2]
                STATUS = i[3]
                STOCK = i[4]
                if STATUS=='0':
                    STATUS=u'閒置中'
                elif STATUS=='1':
                    STATUS=u'已借出'
                    ORG = dbconn("SELECT ORG FROM USERS WHERE UserID='%s'" % useuser)[0][0]
                    
                html = html + u'''<tr>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>%s</td>
                    <td>
                    <button onclick="del('stock',%s,%s)"><span class="glyphicon glyphicon-trash"></span>刪除</button>
                    </td>
                    </tr> ''' % (useuser,ORG,JIGID,JIGNAME,STATUS,STOCK,JIGID)#u'ID為:%s 治具名為：%s的治具' % (JIGID,JIGNAME))
        else:
            html = html + u'<tr><td>抱歉，根據你提供的條件查詢不到任何數據！</td></tr>'
        html = html + u'</tbody></table></div>'
        self.write(html)
                    
class new(LoginHandler):
    @tornado.web.authenticated
    def post(self):
        userid = self.get_argument('userid','')
        jignum = self.get_argument('jignum','')
        line = self.get_argument('line','')
        use = self.get_argument('use','')
        select = self.get_argument('select','')
        remark = self.get_argument('remark','')
        if not line:line=' '
        if not use:use= ' '
        if not remark:remark=' '
        print userid,jignum,line,use,select,remark
        if "" in [userid,jignum,select]:
            self.write("新增失敗，請確認！")
        else:
            try:
                a = dbconn("SELECT JIGID,JIGNAME,STATUS FROM stock where JIGID='%s'" % jignum)
                STATUSUP = False
                if not a:
                    self.write(u"錯誤：未知的治具ID！")
                elif int(userid) not in [i[0] for i in dbconn("SELECT UserID FROM USERS")]:
                    self.write(u"錯誤：借用者工號在人事資料中無記錄，請維護人事資料後再執行本操作！")
                else:
                    STATUS=a[0][2]      #獲取STATUS的值：0表示治具还閒置中，1表示已借出
                    if select =='return':
                        if STATUS=='0':
                            Event=u'歸還記錄寫入異常：無借出記錄，如確認已經借出，請補錄借出記錄之後再嘗試本操作'
                        elif STATUS=='1':
                            STATUSUP = True
                            Event=u'已归还'
                            STATUS='0'
                    elif select =='lend':
                        if STATUS=='1':
                            Event=u'借出記錄寫入異常：這個治具之前已借出，但沒有歸還記錄，如確定已歸還，請補錄歸還記錄后再嘗試本操作'
                        else:
                            STATUSUP = True
                            Event=u'已借出'
                            STATUS='1'
                    if STATUSUP:
                        dbconn("INSERT INTO Log (Owner,TIME,Event,JIGID,remark) VALUES ('%s',datetime('now','localtime'),'%s','%s','%s')" % (userid,Event,jignum,remark))
                        if STATUS=='0':
                            dbconn("UPDATE stock SET use='',line='',UseUser='',STATUS='0' where JIGID='%s'" % jignum)
                        else:
                            dbconn("UPDATE stock SET use='%s',line='%s',UseUser='%s',OUTTIME=datetime('now','localtime'),STATUS='1' where JIGID='%s'" % (use,line,userid,jignum))
                    self.write('[治具ID:%s] %s' % (jignum,Event))
            except Exception as e:
                self.write("新增失敗:%s" % e)
class add(LoginHandler):
    @tornado.web.authenticated
    def post(self):
        jigid = self.get_argument('jigid','')
        jigname = self.get_argument('jigname','')
        jiglist = dbconn("SELECT JIGID FROM stock")
        if None in (jigid,jigname):
            self.write(u"添加失敗：資料提交有誤！")
        elif jigid in [i[0] for i in jiglist]:
            self.write(u"添加失敗：資料庫中已經存在的治具條碼！")
        else:
            dbconn("INSERT INTO stock (JIGID,JIGNAME,STATUS) VALUES ('%s','%s','0')" % (jigid,jigname))
            self.write(u"0")
            
class users(LoginHandler):
    @tornado.web.authenticated
    def post(self):
        oldpwd = self.get_argument('oldpwd','')
        newpwd = self.get_argument('newpwd','')
        newpwd2 = self.get_argument('newpwd2','')
        user = self.get_argument('user','')
        if "" in [oldpwd,newpwd,newpwd2,user]:
            self.write(u"資料提交有誤，請確認！")
        elif oldpwd==dbconn("SELECT Password FROM Ver WHERE UserName='%s'" % user)[0][0]:
            if newpwd==newpwd2!="":
                dbconn("UPDATE Ver SET Password='%s' WHERE UserName='%s'" % (newpwd2,user))
                self.write(u"0")
            else:
                self.write(u"資料提交有誤，請確認！")
        else:
            self.write("舊密碼錯誤，請確認！")
class hr(LoginHandler):
    @tornado.web.authenticated
    def post(self):
        Request = self.get_argument('Request','')
        if Request=='list':
            LIST = dbconn("SELECT * FROM USERS")
            html = u'''<div class="table-responsive div">
            <table id="querytable" class="table table-striped table-bordered table-hover">
            <thead>
            <tr>
            <td>ID</td>
            <td>工號</td>
            <td>姓名</td>
            <td>單位</td>
            <td>備註</td>
            <td>管理</td>
            </tr>
            </thead>
            <tbody>
            '''
            for i in LIST:
                html = html + u'''
                <tr>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td><button onclick="del('hr','%s','%s')"><span class="glyphicon glyphicon-trash"></span>刪除</button></td>
                </tr>
                ''' % (i[0],i[1],i[2],i[3],i[4],i[0],i[2])
            html = html + u'</tbody></table></div>'
            self.write(html)
        elif Request=='sub':
            newid = self.get_argument('newid','')
            newname = self.get_argument('newname','')
            neworg = self.get_argument('neworg','')
            userlist = dbconn("SELECT UserID FROM USERS")
            if int(newid) in [i[0] for i in userlist]:
                self.write(u"資料庫中已存在：%s的記錄！" % newid)
            else:
                dbconn("INSERT INTO USERS (UserID,USERNAME,ORG) VALUES ('%s','%s','%s')" % (newid,newname,neworg))
                self.write(u'用戶：%s-%s 已成功錄入數據庫！' % (newid,newname))
                
class delete(LoginHandler):
    @tornado.web.authenticated
    def post(self):
        TYPE = self.get_argument('TYPE','')
        delid = self.get_argument('delid','')
        SQL=''
        if TYPE=='stock':
            SQL = "delete from stock where ID='%s'" % delid
        elif TYPE=='hr':
            user = dbconn("SELECT UserID FROM USERS WHERE ID='%s'" % delid)[0][0]
            if user in [i[0] for i in dbconn("SELECT UseUser FROM stock")]:
                self.write(u"當前要刪除的用戶有治具資產未歸還，需該用戶歸還治具后方可執行刪除！")
            else:
                SQL = "delete from USERS where ID='%s'" % delid
        if SQL !='':
            try:
                dbconn(SQL)
            except:
                self.write(u"刪除操作失敗！")
            else:
                self.write(u"刪除成功！")
        else:
            self.write(u"發生未知錯誤！")
    
class login(tornado.web.RequestHandler):
    def post(self):
        username = self.get_argument('username','')
        password = self.get_argument('password','')
        try:
            if password == dbconn("select Password from Ver where UserName='%s'" % username)[0][0]:
                self.set_cookie("LoginStock",username)
                self.redirect("/")
            else:
                self.write("0")
        except Exception as e:
            print e
            self.write("0")
    def get(self):
        self.redirect("/")
class logout(tornado.web.RequestHandler):
    def get(self):
        self.clear_all_cookies()
        self.redirect("/")

App = tornado.web.Application([
    (r'/',MainHandler),
    (r'/query',query),
    (r'/new',new),
    (r'/add',add),
    (r'/users',users),
    (r'/hr',hr),
    (r'/del',delete),
    (r'/login',login),
    (r'/logout',logout)
    ],**settings)
if __name__ == "__main__":
    tornado.options.define("port", default=80, help="Run server on a specific port", type=int)
    tornado.options.parse_command_line()
    App.listen(tornado.options.options.port)
    tornado.ioloop.IOLoop.instance().start()
